package org.lzy.pt;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.jivesoftware.database.DbConnectionManager;

public class AreaManager {

	private static final String LOAD_CONTINENT = "SELECT * FROM oftContinent";

	private static final String LOAD_COUNTRY = "SELECT * FROM oftCountry";
	
	private static final String LOAD_CONTINENT_NO_BY_COUNTRY = "SELECT * FROM oftCountry WHERE T_NO=?";
	
	private static final String LOAD_COUNTRY_BY_CONTINENT_NO = "SELECT * FROM oftCountry WHERE T_ContinentNo=?";

	private static AreaManager areaManager = new AreaManager();

	private AreaManager() {
	}

	public static AreaManager getInstance() {
		return areaManager;
	}

	public List<Map<String,Object>> getContinent() throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<Map<String, Object>> list = runner.query(connection,LOAD_CONTINENT, new MapListHandler());
    	DbConnectionManager.closeConnection(connection);
    	return list;
	}
	
	public List<Map<String,Object>> getCountry(int continentNo) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<Map<String, Object>> list = null ;
    	if(continentNo == 0){
    		list = runner.query(DbConnectionManager.getConnection(),LOAD_COUNTRY, 
    				new MapListHandler());
    	}else{
    		list = runner.query(DbConnectionManager.getConnection(),LOAD_COUNTRY_BY_CONTINENT_NO, 
    				new MapListHandler(),continentNo);
    	}
    			
    	DbConnectionManager.closeConnection(connection);
    	return list;
	}
	
	public Map<String, Object> getContinentByCountry(int id) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	Map<String, Object> result = runner.query(connection,LOAD_CONTINENT_NO_BY_COUNTRY, new MapHandler(),id);
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
}
